﻿using System;
using System.Data;
using System.Data.SQLite;
namespace DrawImage
{
    public static class DBHelperSQLite
    {
        private static string _ConnectionString = @"data source = imagedata.sqlite";// 补全id password database
        public static string ConnectionString { get => _ConnectionString; set => _ConnectionString = value; }
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString, params SQLiteParameter[] cmdParms)
        {
            using (SQLiteConnection connection = new SQLiteConnection(ConnectionString)) {
                SQLiteCommand cmd = new SQLiteCommand();
                SQLString = @"CREATE TABLE if not exists `images` (
                                    `name` varchar(50) DEFAULT NULL,
                                    `id` varchar(20) NOT NULL,
                                    `longitude` float DEFAULT NULL,
                                    `latitude` float DEFAULT NULL,
                                    `altitude` float DEFAULT NULL,
                                    `height` float DEFAULT NULL,
                                    `distance` float DEFAULT NULL,
                                    `angle` float DEFAULT NULL,
                                    `position` int(11) DEFAULT NULL,
                                    PRIMARY KEY(`id`)
                                ); " + SQLString;
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd)) {
                    DataSet ds = new DataSet();
                    try {
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                    }
                    catch (System.Data.SQLite.SQLiteException ex) {
                        throw new Exception(ex.Message);
                    }
                    return ds;
                }
            }
        }
        private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, SQLiteTransaction trans, string cmdText, SQLiteParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null) {
                foreach (SQLiteParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }
    }
}
